package com.friday.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.friday.dao.VacateApplyDao;
import com.friday.po.VacateApply;
import com.friday.util.DBUtil;

public class VacateApplyDaoImpl implements VacateApplyDao {
	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pStmt = null;
	private ResultSet rs = null;
	private DBUtil util = null;
	
	@Override
	public int insertVacateApply(VacateApply va) throws Exception {
		String sql = "insert into vacateapply values(?,?,?,?,?,?,?,?)";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, va.getId());
			pStmt.setString(2, va.getEmployee());
			pStmt.setString(3, va.getDepartment());
			pStmt.setDate(4, va.getApplyDate());
			pStmt.setDate(5, va.getStartDate());
			pStmt.setDate(6, va.getEndDate());
			pStmt.setString(7, va.getNote());
			pStmt.setInt(8, va.getState());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("vacateApplydao insert error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
				
		return num;
	}

	@Override
	public int updateDifferenceByid(VacateApply va) throws Exception {
		String sql = "update vacateapply set employee = ?, department = ?, applydate = ?,startdate = ?,enddate = ?, note = ?, state = ? where id = ?";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(8, va.getId());
			pStmt.setString(1, va.getEmployee());
			pStmt.setString(2, va.getDepartment());
			pStmt.setDate(3, va.getApplyDate());
			pStmt.setDate(4, va.getStartDate());
			pStmt.setDate(5, va.getEndDate());
			pStmt.setString(6, va.getNote());
			pStmt.setInt(7, va.getState());
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("vacateApplydao update error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
				
		return num;
	}

	@Override
	public int deleteById(String id) throws Exception {
		String sql = "delete from vacateApply where id = ?";
		int num = 0;
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			num = pStmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
//			e.printStackTrace();
			throw new Exception("vacateApplydao update error!");
		}finally{
			util.close(pStmt);
			util.close(conn);
		}
				
		return num;
	}

	@Override
	public VacateApply queryById(String id) throws Exception {
		String sql = "select * from vacateApply where id = ?";
		VacateApply va = new VacateApply();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			conn.setAutoCommit(false);
			pStmt = conn.prepareStatement(sql);
			
			pStmt.setString(1, id);
			
			rs = pStmt.executeQuery();
			
			while(rs.next())
			{
				va.setApplyDate(rs.getDate("applyDate"));
				va.setDepartment(rs.getString("department"));
				va.setEmployee(rs.getString("employee"));
				va.setEndDate(rs.getDate("endDate"));
				va.setId(rs.getString("id"));
				va.setNote(rs.getString("note"));
				va.setStartDate(rs.getDate("startDate"));
				va.setState(rs.getInt("state"));
			}
		} catch (Exception e) {
			throw new Exception("vacateApplydao querybyid error!");
		}finally{
			util.close(rs);
			util.close(pStmt);
			util.close(conn);
		}
				
		return va;
	}

	@Override
	public List<VacateApply> queryAll() throws Exception {
		String sql = "select * from vacateapply";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<VacateApply> queryByEmployee(String employee) throws Exception {
		String sql = "select * from vacateapply where employee = '" + employee + "' order by state";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<VacateApply> queryByDepartment(String department)
			throws Exception {
		String sql = "select * from vacateapply where department = '" + department + "' order by state";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<VacateApply> queryByState(int state) throws Exception {
		String sql = "select * from vacateapply where state = " + state + "";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<VacateApply> queryByEmployeeAndState(String employee, int state)
			throws Exception {
		String sql = "select * from vacateapply where state = " + state + " and employee = '" + employee + "'";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}
	
	@Override
	public List<VacateApply> queryByDepartmentAndState(String department,
			int state) throws Exception {
		String sql = "select * from vacateapply where state = " + state
				+ " and department = '" + department + "'";
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}

	@Override
	public List<VacateApply> queryByApplyDatePeriod(Date firstday, Date lastday)
			throws Exception {
		String sql = "select * from vacateapply where applydate between " + firstday + " and "+lastday;
		List<VacateApply> list = new ArrayList<VacateApply>();
		list = this.queryBySql(sql);
		return list;
	}
	
	private List<VacateApply> queryBySql(String sql) throws Exception
	{
		List<VacateApply> list = new ArrayList<VacateApply>();
		
		try {
			util = DBUtil.newInstance();
			conn = util.getConnection();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while(rs.next())
			{
				VacateApply va = new VacateApply();
				va.setApplyDate(rs.getDate("applyDate"));
				va.setDepartment(rs.getString("department"));
				va.setEmployee(rs.getString("employee"));
				va.setEndDate(rs.getDate("endDate"));
				va.setId(rs.getString("id"));
				va.setNote(rs.getString("note"));
				va.setStartDate(rs.getDate("startDate"));
				va.setState(rs.getInt("state"));
				
				list.add(va);
			}
		} catch (SQLException e) {
			throw new Exception("vacateapplydao query sql error!");
		} catch (Exception e) {
			throw new Exception("vacateapplydao query error!");
		}finally {
			util.close(rs);
			util.close(stmt);
			util.close(conn);		}
		
		return list;
		
	}

}
